﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CSharpDataAccess.Product
{
    /// <summary>
    /// 
    /// </summary>
    /// <remarks>Factory Method: Concrete Product</remarks>
    internal class SqlServer : IDataAccessHandler
    {
        public string StringConnection { get; set; }

        public SqlServer(string stringConnection)
        {
            this.StringConnection = stringConnection;
        }

        public bool Open()
        {
            using (SqlConnection connection = new SqlConnection(StringConnection))
            {
                if (connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    return true;
                }

                return false;
            }
        }

        public bool Close()
        {
            using (SqlConnection connection = new SqlConnection(StringConnection))
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                    return true;
                }
                else if (connection.State == ConnectionState.Closed)
                {
                    return true;
                }

                return false;
            }
        }

        public T ExecuteScalar<T>(CommandType commandType, string commandText)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(StringConnection))
                {
                    SqlCommand command = new SqlCommand(commandText, connection);
                    connection.Open();
                    return (T)command.ExecuteScalar();
                }
            }
            catch (Exception e)
            {
                throw new CSharpException("", e);
            }
            finally
            {

            }
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(StringConnection))
                {
                    SqlCommand command = new SqlCommand(commandText, connection);
                    command.Connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw new CSharpException("", e);
            }
            finally
            {

            }
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            using (SqlConnection connection = new SqlConnection(StringConnection))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(commandText, connection);

                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                return dataset;
            }

        }
      
        public DataTable ExecuteDataTable(CommandType commandType, string commandText)
        {
            using (SqlConnection connection = new SqlConnection(StringConnection))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(commandText, connection);

                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }

        public IEnumerable<T> ExecuteDataReader<T>(CommandBehavior commandBehavior, string commandText, Func<IDataRecord, T> selector)
        {

            using (SqlConnection connection = new SqlConnection(StringConnection))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader(commandBehavior);

                while (reader.Read())
                {
                    yield return selector(reader);
                }
            }
        }

        public IEnumerable<IDataRecord> ExecuteDataReader(CommandBehavior commandBehavior, string commandText)
        {
            using (SqlConnection connection = new SqlConnection(StringConnection))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader(commandBehavior);

                while (reader.Read())
                {
                    yield return reader;
                }
            }
        }
    }
}
